﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class PromotionDeptBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(int prm_seq, int emp_seq, int prm_job_site_seq, int? apr_job_site_seq,
            int promote, int appraisal, string created_by, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@prm_seq", prm_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@prm_job_site_seq", prm_job_site_seq);
                dbManager.AddParameters(3, "@apr_job_site_seq", apr_job_site_seq);
                dbManager.AddParameters(4, "@promote", promote);
                dbManager.AddParameters(5, "@appraisal", appraisal);
                dbManager.AddParameters(6, "@created_by", created_by);
                dbManager.AddParameters(7, "@created_when", DateTime.Now);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Now);

                string strSQL;

                strSQL = " Insert Into hrm_prm_promotion_employees ( " +
                        " prm_seq, " +
                        " emp_seq, " +
                        " prm_job_site_seq, " +
                        (apr_job_site_seq == null ? " apr_job_site_seq, " : "") +
                        " promote, " +
                        " appraisal, " +
                        " created_by, " +
                        " created_when, " +
                        " updated_by, " +
                        " updated_when, " +
                        " record_status) " +
                        " values ( " +
                        " @prm_seq, " +
                        " @emp_seq, " +
                        " @prm_job_site_seq, " +
                        (apr_job_site_seq == null ? " @apr_job_site_seq, " : "") +
                        " @promote, " +
                        " @appraisal, " +
                        " @created_by, " +
                        " @created_when, " +
                        " @updated_by, " +
                        " @updated_when, " + 
                        " 'U')";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_es_move_requests}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Update(int prm_emp_seq, int prm_job_site_seq, int? apr_job_site_seq,
            int promote, int appraisal, string updated_by)
        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@prm_job_site_seq", prm_job_site_seq);
                dbManager.AddParameters(1, "@apr_job_site_seq", apr_job_site_seq);
                dbManager.AddParameters(2, "@promote", promote);
                dbManager.AddParameters(3, "@appraisal", appraisal);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);
                dbManager.AddParameters(6, "@prm_emp_seq", prm_emp_seq);

                string sql;
                sql = " Update hrm_prm_promotion_employees set " +
                        " prm_job_site_seq = @prm_job_site_seq, " +
                        " apr_job_site_seq = @apr_job_site_seq, " +
                        " promote = @promote, " +
                        " appraisal = @appraisal, " +
                        " updated_by = @updated_by, " +
                        " updated_when = @updated_when  " +
                        " WHERE prm_emp_seq = @prm_emp_seq";
                dbManager.ExecuteNonQuery(CommandType.Text,sql) ;  

             }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();  
            }
        
        
        }

        public void Delete(int prm_emp_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@prm_emp_seq", prm_emp_seq);
                dbManager.AddParameters(1, "@updated_by", updated_by);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                string sql;
                sql = " Update hrm_prm_promotion_employees set " +
                    " record_status = @record_status, " +
                    " updated_by = @updated_by " +
                    " WHERE prm_emp_seq = @prm_emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable select()
        {

            try
            {
                string sql;
                sql = " SELECT " +
                        " hrm_prm_promotion_employees.prm_emp_seq, " +
                        " mas_name_prefixes.prefix_name, " +
                        " hrm_ps_employees.first_name, " +
                        " hrm_ps_employees.last_name, " +
                        " hrm_mp_positions.pos_name, " +
                        " hrm_mp_classes.class_name, " +
                        " hrm_mp_job_sites3.job_site_name, " +
                        " case hrm_prm_promotion_employees.appraisal when 1 then 'ประเมิน' when 0 then 'ไม่ประเมิน' else '' end as appraisal, " +
                        " hrm_mp_job_sites2.job_site_name as apr_job_site_name, " +
                        " case hrm_prm_promotion_employees.promote when 1 then 'เลื่อน' when 0 then 'ไม่เลื่อน' else '' end as promote, " +
                        " hrm_mp_job_sites.job_site_name as prm_job_site_name " +
                        " FROM " +
                        " hrm_prm_promotion_employees " +
                        " Inner Join hrm_ps_employees ON hrm_prm_promotion_employees.emp_seq = hrm_ps_employees.emp_seq " +
                        " Inner Join hrm_prm_promotions ON hrm_prm_promotion_employees.prm_seq = hrm_prm_promotions.prm_seq " +
                        " Inner Join hrm_mp_job_sites ON hrm_prm_promotion_employees.prm_job_site_seq = hrm_mp_job_sites.job_site_seq " +
                        " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " Inner Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq " +
                        " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_prm_promotion_employees.apr_job_site_seq = hrm_mp_job_sites2.job_site_seq " +
                        " Inner Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq " +
                        " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites3.job_site_seq " +
                        " WHERE " +
                        " 1=2 ";
            
                //where emp_seq = " + emp_seq;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();  
            }
        }

        public string GetDepartName(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT hrm_mp_job_sites2.job_site_name " + 
                        " FROM (SELECT " +
                        " 	ifNULL(hrm_ps_employees.working_job_site_seq,  " +
                        " 	hrm_mp_positions.job_site_seq) as working_job_site_seq  " +
                        " 	FROM  " +
                        " 	hrm_ps_employees  " +
                        " 	Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " 	WHERE hrm_ps_employees.emp_seq = @emp_seq) as T1  " +
                        " 	Inner Join hrm_mp_job_sites ON T1.working_job_site_seq = hrm_mp_job_sites.job_site_seq " +
                        " 	Inner Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_mp_job_sites.upper_job_site_seq = hrm_mp_job_sites2.job_site_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["job_site_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose(); 
            }
        }
        
        public int GetJobSiteSeq(int EmpSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_seq", EmpSeq);
                string sql;
                sql = " SELECT " +
                        " ifNULL(hrm_ps_employees.working_job_site_seq, " +
                        " hrm_mp_positions.job_site_seq) as working_job_site_seq " +
                        " FROM " +
                        " hrm_ps_employees " +
                        " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " WHERE " +
                        " hrm_ps_employees.emp_seq = @emp_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["working_job_site_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetYearType(int empTypeSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@emp_type_seq", empTypeSeq);
                string sql;
                sql = " SELECT " +
                        " hrm_ps_employee_types.year_type " +
                        " FROM " +
                        " hrm_ps_employee_types " +
                        " WHERE " +
                        " hrm_ps_employee_types.emp_type_seq =  @emp_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["year_type"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable select(int PrmYear, int prmPeriod, int empTypeSeq, int JobSiteSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@prm_year", PrmYear);
                dbManager.AddParameters(1, "@prm_period", prmPeriod);
                dbManager.AddParameters(2, "@emp_type_seq", empTypeSeq);
                dbManager.AddParameters(3, "@job_site_seq", JobSiteSeq);
                string sql;
                sql = " SELECT prm_emp_seq,prefix_name,first_name,last_name,pos_name,class_name,job_site_name,appraisal_name,apr_job_site_name,promote_name,prm_job_site_name,appraisal,apr_job_site_seq,promote,prm_job_site_seq " +
                        " FROM  ( SELECT " +
                        " hrm_prm_promotion_employees.prm_emp_seq, " +
                        " mas_name_prefixes.prefix_name, " +
                        " hrm_ps_employees.first_name, " +
                        " hrm_ps_employees.last_name, " +
                        " hrm_mp_positions.pos_name, " +
                        " hrm_mp_classes.class_name, " +
                        " ifNULL(hrm_mp_job_sites3.job_site_name,hrm_mp_job_sites4.job_site_name) as job_site_name, " +
                        " ifNULL(hrm_ps_employees.working_job_site_seq,hrm_mp_job_sites4.job_site_seq) as working_job_site_seq, " +
                        " case hrm_prm_promotion_employees.appraisal when 1 then 'ประเมิน' when 0 then 'ไม่ประเมิน' else '' end as appraisal_name, " +
                        " hrm_mp_job_sites2.job_site_name as apr_job_site_name, " +
                        " case hrm_prm_promotion_employees.promote when 1 then 'เลื่อน' when 0 then 'ไม่เลื่อน' else '' end as promote_name, " +
                        " hrm_mp_job_sites.job_site_name as prm_job_site_name, " +
                        " hrm_prm_promotion_employees.appraisal, " +
                        " hrm_mp_job_sites2.job_site_seq as apr_job_site_seq, " +
                        " hrm_prm_promotion_employees.promote , " +
                        " hrm_mp_job_sites.job_site_seq as prm_job_site_seq, " +
                        " hrm_prm_promotions.prm_year, " +
                        " hrm_prm_promotions.prm_period, " +
                        " hrm_prm_promotions.emp_type_seq , " +
                        " hrm_prm_promotion_employees.record_status " +
                        " FROM " +
                        " hrm_prm_promotion_employees " +
                        " Inner Join hrm_ps_employees ON hrm_prm_promotion_employees.emp_seq = hrm_ps_employees.emp_seq " +
                        " Inner Join hrm_prm_promotions ON hrm_prm_promotion_employees.prm_seq = hrm_prm_promotions.prm_seq " +
                        " Inner Join hrm_mp_job_sites ON hrm_prm_promotion_employees.prm_job_site_seq = hrm_mp_job_sites.job_site_seq " +
                        " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq " +
                        " Inner Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq " +
                        " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_prm_promotion_employees.apr_job_site_seq = hrm_mp_job_sites2.job_site_seq " +
                        " Inner Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq " +
                        " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites3.job_site_seq " +
                        " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites4  ON hrm_mp_positions.job_site_seq = hrm_mp_job_sites4.job_site_seq) as T1 " +
                        " WHERE " +
                        " prm_year =  @prm_year AND " +
                        " prm_period =  @prm_period AND " +
                        " emp_type_seq =  @emp_type_seq AND " +
                        " working_job_site_seq = @job_site_seq AND " +
                        " record_status = 'U' ";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetEmployeeTypes()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = "SELECT EMP_TYPE_SEQ, EMP_TYPE_NAME, REMARKS, CREATED_BY, UPDATED_BY"
                    + " FROM HRM_PS_EMPLOYEE_TYPES"
                    + " WHERE RECORD_STATUS not like @DeleteRecord";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable GetAppraisals(int Year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@apr_year", Year);

                //string strSQL = " SELECT " +
                //                " hrm_apr_appraisals.apr_period, " +
                //                " concat(hrm_apr_appraisals.apr_name,'  ', " +
                //                " cast(hrm_apr_appraisals.act_start_date as char),' ถึง ', " +
                //                " cast(hrm_apr_appraisals.act_end_date as char)) as apr_name " +
                //                " FROM " +
                //                " hrm_apr_appraisals " +
                //                " WHERE " +
                //                " hrm_apr_appraisals.apr_year =  @apr_year " +
                //                " Order By apr_period";
                string strSQL = " SELECT " +
                                " hrm_apr_appraisals.apr_period, " +
                                " hrm_apr_appraisals.apr_name, " +
                                " hrm_apr_appraisals.act_start_date, " +
                                " hrm_apr_appraisals.act_end_date " +
                                " FROM  " +
                                " hrm_apr_appraisals  " +
                                " WHERE  " +
                                " hrm_apr_appraisals.apr_year =  @apr_year " +
                                " Order By apr_period";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetYearTypeName(int yearTypeSeq)
        {
            try 
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@year_type_seq", yearTypeSeq);
                string sql;
                sql = " SELECT " +
                        " sys_year_type.year_type_name " +
                        " FROM " +
                        " sys_year_type " +
                        " Inner Join hrm_ps_employee_types ON hrm_ps_employee_types.year_type_seq = sys_year_type.year_type_seq " +
                        " WHERE " +
                        " hrm_ps_employee_types.emp_type_seq = @year_type_seq";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0].Rows[0]["year_type_name"].ToString();
            }
            catch
            {
                return "";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetPrmPromotionSeq(int EmpTypeSeq,int PrmYear,int PrmPriod)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_type_seq", EmpTypeSeq);
                dbManager.AddParameters(1, "@prm_year", PrmYear);
                dbManager.AddParameters(2, "@prm_period", PrmPriod);
                string sql;
                sql = " SELECT " +
                        " hrm_prm_promotions.prm_seq " +
                        " FROM " +
                        " hrm_prm_promotions " +
                        " WHERE " +
                        " hrm_prm_promotions.emp_type_seq =  @emp_type_seq AND " +
                        " hrm_prm_promotions.prm_year =  @prm_year AND " +
                        " hrm_prm_promotions.prm_period =  @prm_period";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["prm_seq"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
